﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using VFNStockTrader.DAL.Oracle.Helper;
using System.Net;
using System.IO;
using XchangeStreamer.Types;

namespace VFNStockTrader.DAL.Oracle.MarketDataFeed.EGX
{
    class RawData
    {
        #region Private Fields

        #region Connections List

        private OracleConnection _internalConnection;
        private string conString;

        #endregion

        #region SQL Statments

        private const string SQL_SELECT_RAWDATA_GETORDERS = @"SELECT    ORDER_DATE, SYMBOL_CODE, MARKET_CODE, ORDER_NUMBER, ORDER_PRICE, VOLUME_TRADED, 
                                                                        ORDER_VALUE, LE_VALUE, EXEC_TIME, ORDER_TYPE, EXCHANGE, ORDER_ACTION, 
                                                                        REC_SERIAL
                                                                FROM ORDERS
                                                                WHERE REC_SERIAL > :LAST_REC_SERIAL
                                                                ORDER BY REC_SERIAL ASC";

        private const string SQL_SELECT_RAWDATA_GETTRADES = @"SELECT    TRADE_DATE, SYMBOL_CODE, MARKET_CODE, TICKET_NUMBER, TRADE_PRICE, VOLUME_TRADED,
                                                                        TRADE_VALUE, LE_VALUE, EXEC_TIME, BUY_ORDER_NUMBER, SELL_ORDER_NUMBER
                                                                FROM TRADES
                                                                WHERE TICKET_NUMBER > :LAST_TICKET_NUMBER
                                                                ORDER BY TICKET_NUMBER ASC";

        private const string SQL_SELECT_MARKETSUMMARY_GETMARKETSUMMARY = @"SELECT  SUM(TR.VOLUME_TRADED) AS VOLUME, COUNT(*) AS TRADES , 
                                                                                    COUNT(DISTINCT TR.SYMBOL_CODE) AS SYMBOLSTRADED , SUM(TR.LE_VALUE) AS  TURNOVER, 'SHARES' AS TYPE 
                                                                            FROM   TRADES TR, SYMBOLSUMMARY SS 
                                                                            WHERE   TR.SYMBOL_CODE = SS.SYMBOL_CODE   
                                                                            AND     SYMBOL_TYPE IN (210)
                                                                            UNION
                                                                            SELECT  SUM(TR.VOLUME_TRADED) AS VOLUME, COUNT(*) AS TRADES , 
                                                                                    COUNT(DISTINCT TR.SYMBOL_CODE) AS SYMBOLSTRADED , SUM(TR.LE_VALUE) AS  TURNOVER, 'BONDS' AS TYPE 
                                                                            FROM   TRADES TR, SYMBOLSUMMARY SS 
                                                                            WHERE   TR.SYMBOL_CODE = SS.SYMBOL_CODE   
                                                                            AND     SYMBOL_TYPE = 214
                                                                            UNION
                                                                            SELECT  SUM(TR.VOLUME_TRADED) AS VOLUME, COUNT(*) AS TRADES , 
                                                                                    COUNT(DISTINCT TR.SYMBOL_CODE) AS SYMBOLSTRADED , SUM(TR.LE_VALUE) AS  TURNOVER, 'OTC' AS TYPE 
                                                                            FROM   TRADES TR, SYMBOLSUMMARY SS 
                                                                            WHERE   TR.SYMBOL_CODE = SS.SYMBOL_CODE   
                                                                            AND     SYMBOL_TYPE = 1210 
                                                                            UNION
                                                                            SELECT  SUM(TR.VOLUME_TRADED) AS VOLUME, COUNT(*) AS TRADES , 
                                                                                    COUNT(DISTINCT TR.SYMBOL_CODE) AS SYMBOLSTRADED , SUM(TR.LE_VALUE) AS  TURNOVER, 'FUND' AS TYPE 
                                                                            FROM   TRADES TR, SYMBOLSUMMARY SS 
                                                                            WHERE   TR.SYMBOL_CODE = SS.SYMBOL_CODE   
                                                                            AND     SYMBOL_TYPE = 217 
                                                                            UNION
                                                                            SELECT  SUM(TR.VOLUME_TRADED) AS VOLUME, COUNT(*) AS TRADES , 
                                                                                    COUNT(DISTINCT TR.SYMBOL_CODE) AS SYMBOLSTRADED , SUM(TR.LE_VALUE) AS  TURNOVER, 'SME' AS TYPE 
                                                                            FROM   TRADES TR, SYMBOLSUMMARY SS 
                                                                            WHERE   TR.SYMBOL_CODE = SS.SYMBOL_CODE   
                                                                            AND     SYMBOL_TYPE = 2210";

        private const string SQL_SELECT_MARKETINFO_GETMARKETINFO = @"SELECT 'EGX30' AS INDEXID, EGX_STATUS , TRUNC(SYSDATE) AS DAY ,
                                                                        (SELECT   TO_CHAR(MAX( INDEXVALUE ), '9,999,999.00' )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' )   MAXVALUE,
                                                                        (SELECT   TO_CHAR(MIN( INDEXVALUE ), '9,999,999.00' )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' )   MINVALUE,
                                                                        (SELECT   TO_CHAR(MAX( INDEXVALUE ), '9,999,999.00' )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MIN( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'  AND INDEXVALUE IS NOT NULL ))  OPENVALUE ,
                                                                        (SELECT   TO_CHAR(MAX( INDEXVALUE ), '9,999,999.00' )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MAX( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' ))  CURRENTVALUE ,
                                                                        (SELECT   MAX( INDEXVALUE )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MAX( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' ))
                                                                          -   (SELECT   MAX( INDEXVALUE )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MIN( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' AND INDEXVALUE IS NOT NULL))  NETCHANGE ,
                                                                        TO_CHAR(
                                                                        (   (SELECT   MAX( INDEXVALUE )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MAX( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' AND INDEXVALUE IS NOT NULL))
                                                                          -   (SELECT   MAX( INDEXVALUE )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MIN( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' AND INDEXVALUE IS NOT NULL)))
                                                                          /   (SELECT   MAX( INDEXVALUE )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30'   AND  INDEXTIME =   (SELECT   MIN( INDEXTIME )   FROM  CASEINDEX WHERE  INDEXCODE =   'EGX30' AND INDEXVALUE IS NOT NULL))  * 100
                                                                          , '90.09' )
                                                                         CHANGEPERC ,
                                                                        (SELECT   TO_CHAR(SUM(   CUM_VOLUME), '9,999,999,999,999' )   FROM  ISSUES WHERE  SYMBOL_CODE IN   (SELECT  SYMBOL_CODE FROM  CASE30_COMPANIES ))  VOLUME ,
                                                                        (SELECT   TO_CHAR(SUM( CUM_TRADE_VALUE ), '9,999,999,999,999.00' )   FROM  ISSUES WHERE  SYMBOL_CODE IN   (SELECT  SYMBOL_CODE FROM  CASE30_COMPANIES ))   VALUE,
                                                                        TO_CHAR(SYSDATE, 'HH24:MI:SS') AS INDEXTIME
                                                                        FROM  DUAL";


        private const string SQL_SELECT_MARKETINFO_GETMARKETSTATUS = @"SELECT  EGX_STATUS , TRUNC(SYSDATE) AS DAY ,
                                                                        TO_CHAR(SYSDATE, 'HH24:MI:SS') AS INDEXTIME,egx_TimeToClose,egx_TimeToOpen, egx_PreviousClose,egx_NextOpen
                                                                        FROM  DUAL";

        private const string SQL_SELECT_SYMBOLSUMMARY_GETSYMBOLSUMMARY = @"select symbol_code ,close_price, cum_num_transact, cum_trade_value,
                                                                                  cum_volume, high_price, low_price, ref_price, symbol_type, SYSDATE As RefDateTime
                                                                           from symbolsummary
                                                                           where (select sum(cum_num_transact) from symbolsummary) <> :TotalTrades";



        private const string SQL_SELECT_SYMBOLINFO_GETSYMBOLINFO = @"SELECT SI.SYMBOL_CODE, SI.CURRENCY, SI.LISTED_SHARES, SI.PAID_SHARES, 
                                                                        SI.FACE_VALUE, SI.COUPON_DATE, SI.COUPON_VALUE
                                                                        , SI.NET_PROFIT, SI.NET_PROFIT_DATE
                                                                        , SI.PE_RATIO, SI.DIVIDEND_YIELD_PERC, SI.MARKET_CAP
                                                                        , SI.SECTOR_ID, SI.EPS, S.ENG_DESC, SI.INDEX_ID, SI.SYMBOL_STATE
                                                                        , SI.LAST_TRADE_DATE, SI.REUTERS, SI.ENG_NAME, SI.ARB_NAME, SI.SYMBOL_TYPE
                                                                        FROM
                                                                        (
                                                                        SELECT SI.SYMBOL_CODE, SI.SYMBOL_TYPE, SI.SECTOR_ID, 
                                                                           SI.ENG_NAME, SI.ARB_NAME, SI.ISIN, 
                                                                           SI.REUTERS, SI.CURRENCY, SI.BOND_TYPE, 
                                                                           SI.LISTED_SHARES, SI.PAID_SHARES, SI.FACE_VALUE, 
                                                                           SI.COUPON_VALUE, SI.COUPON_DATE, SI.NET_PROFIT, 
                                                                           SI.NET_PROFIT_DATE, SI.EPS, SI.PE_RATIO, 
                                                                           SI.LAST_TRADE_DATE, SI.DIVIDEND_YIELD_PERC, SI.MARKET_CAP, 
                                                                           SI.OPEN_PRICE, SI.INDEX_ID, I.SYMBOL_STATE
                                                                        FROM
                                                                        (
                                                                        SELECT SI.SYMBOL_CODE, SI.SYMBOL_TYPE, SI.SECTOR_ID, 
                                                                           SI.ENG_NAME, SI.ARB_NAME, SI.ISIN, 
                                                                           SI.REUTERS, SI.CURRENCY, SI.BOND_TYPE, 
                                                                           SI.LISTED_SHARES, SI.PAID_SHARES, SI.FACE_VALUE, 
                                                                           SI.COUPON_VALUE, SI.COUPON_DATE, SI.NET_PROFIT, 
                                                                           SI.NET_PROFIT_DATE, SI.EPS, SI.PE_RATIO, 
                                                                           SI.LAST_TRADE_DATE, SI.DIVIDEND_YIELD_PERC, SI.MARKET_CAP, 
                                                                           SI.OPEN_PRICE, INDX.INDEX_ID
                                                                        FROM
                                                                        SYMBOLINFO SI
                                                                        LEFT OUTER JOIN
                                                                        (
                                                                        SELECT SI.SYMBOL_CODE AS SYMBOL, '30' AS INDEX_ID
                                                                        FROM SYMBOLINFO SI
                                                                        INNER JOIN
                                                                        CASE30_COMPANIES CC
                                                                        ON SI.SYMBOL_CODE = CC.SYMBOL_CODE

                                                                        UNION

                                                                        SELECT SI.SYMBOL_CODE AS SYMBOL, '70' AS INDEX_ID
                                                                        FROM SYMBOLINFO SI
                                                                        INNER JOIN
                                                                        EGX70_SYMBOLS S70
                                                                        ON SI.SYMBOL_CODE = S70.SYMBOL_CODE
                                                                        ) INDX
                                                                        ON SI.SYMBOL_CODE = INDX.SYMBOL
                                                                        ) SI
                                                                        LEFT OUTER JOIN
                                                                        ISSUES I
                                                                        ON SI.SYMBOL_CODE = I.SYMBOL_CODE
                                                                        ) SI
                                                                        LEFT OUTER JOIN
                                                                        SECTORS S
                                                                        ON SI.SECTOR_ID = S.SECTOR_CODE";

        private string SQL_SELECT_INVESTORS_GETINVESTORS = @"select SELL_CLIENT,BUY_VALUE,SELL_VALUE,NET_VALUE,BUY_PERC,SELL_PERC,TOTAL_PERC,UPDATE_DATE from investors where UPDATE_DATE > :UpdateDate ORDER BY UPDATE_DATE ASC";


        private string SQL_SELECT_INSTITATIONS_GETINSTITATIONS = @"SELECT  RAT_FOR,RAT_EG,RAT_RET,RAT_COM,TO_CHAR(TO_TIMESTAMP(TRADE_TIME, 'HH24:MI:SS'), 'HH24:MI:SS') as TradeTime,RAT_BUY_FOR,RAT_SELL_FOR,
                                                                                          RAT_BUY_EG,RAT_SELL_EG,RAT_BUY_RAT,RAT_SELL_RAT,RAT_BUY_COM,RAT_SELL_COM,
                                                                                          BUY_F,SELL_F,BUY_EG,SELL_EG,BUY_RET,SELL_RET,BUY_COM,SELL_COM,TOTAL_VALUE,
                                                                                          FOR_TOTAL,EG_TOTAL,BUY_ARB,SELL_ARB,BUY_OTHER,SELL_OTHER
                                                                                          FROM DAILY_FOR_RET_VIEW where TRADE_TIME > :TradeTime order by TradeTime Asc";




        private string SQL_SELECT_NEWS_GETNEWS = @"select ne.CODE, ne.CONTENT, ne.DATE_STAMP, ne.END_DATE, ne.HEADING, ne.MATERIAL_EVENT,
ne.SEC_ID, ne.SHORT_DESC, ne.START_DATE, ne.SYMBOL_CODE, se.SECTION
 from news ne, sections se 
 where ne.SEC_ID (+)= se.SEC_ID  
 and ne.CODE> :Code  and ne.SEC_ID = '4'
 and TRUNC (ne.DATE_STAMP) >= TRUNC ((SELECT MAX (DATE_STAMP) FROM NEWS where SEC_ID = '4'))
  order by date_stamp asc";


        private string SQL_SELECT_SECTORS_GETSECTORS = @"select SECTOR_CODE,ENG_DESC,ARABIC_DESC, SYSDATE As RefDateTime from SECTORS";
        private string SQL_SELECT_INDICES_GETINDICES = @"select INDEXCODE, INDEXTIME, INDEXVALUE from CASEINDEX  where INDEXTIME > :IndexTime
            order by INDEXTIME asc";

        private string SQL_SELECT_BULLETIN_GETBULLETIN_FIRST = @"select b.BCODE, b.BMSG, b.BMSGARB, b.BHEAD, b.BHEADARB, b.SYMBOL_CODE,
       b.BURL, b.BDATE, b.BTIME, t.TYPE_DESC, b.BTYPE 
from bulletin b , bulletin_type t
where b.BTYPE = t.TYPE_CODE 
and trunc(b.BDATE) = (SELECT  trunc(MAX(BDATE)) FROM bulletin) order by b.BDATE asc";

        private string SQL_SELECT_BULLETIN_GETBULLETIN = @"select b.BCODE, b.BMSG, b.BMSGARB, b.BHEAD, b.BHEADARB, b.SYMBOL_CODE,
       b.BURL, b.BDATE, b.BTIME, t.TYPE_DESC, b.BTYPE 
from bulletin b , bulletin_type t
where b.BTYPE = t.TYPE_CODE 
and b.BDATE > :MaxTime order by b.BDATE asc";

        private string SQL_SELECT_CaseSectorIndex_GETRawCaseSectorIndex = @"select  sector_Code,
ROUND(NVL (index_value, 0),4)
,trade_date, SYSDATE As RefDateTime from CASE_NEW_SECTOR_INDX
     order by trade_date asc";

        private string SQL_SELECT_Symbol52HighLow_GETSymbol52HighLow = @" SELECT 
  symbol_code,high_price,high_date,low_price,low_date, SYSDATE As RefDateTime
FROM SYMBOL52HIGHLOW";

        private string SQL_SELECT_RAWDATA_GETSYMBOLSSHORTLIST = @"SELECT SYMBOL_CODE, REUTERS, ENG_NAME, ARB_NAME
                                                                    FROM SYMBOLINFO
                                                                    WHERE (SELECT COUNT(*) FROM SYMBOLINFO) > :LAST_COUNT";

        #endregion

        #endregion

        #region Constructor

        public RawData()
        {
            try
            {
                this._internalConnection = new OracleConnection();
            }
            catch (Exception ex)
            {
            }
            //InitConnections();
        }

        #endregion

        #region IRawData Members

        public void Open(string connString)
        {
            try
            {
                this.conString = connString;
                if (this._internalConnection == null)
                {
                    this._internalConnection = new OracleConnection();
                }

                this._internalConnection.ConnectionString = connString;
                this._internalConnection.Open();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public void Close()
        {
            try
            {
                if (this._internalConnection != null && this._internalConnection.State != ConnectionState.Closed)
                {
                    this._internalConnection.Close();
                }
            }
            catch (Exception ex)
            {
            }
        }

        public List<Order> GetOrders(int lastRecSerial)
        {
            List<Order> orders = new List<Order>();
            OracleSafeDataReader dr;

            OracleParameter param = new OracleParameter(":LAST_REC_SERIAL", OracleDbType.Int32);
            param.Value = lastRecSerial;

            dr = VFNStockTrader.DAL.Oracle.Helper.Oracle.ExecuteReaderSingleParm(this._internalConnection, null, CommandType.Text,
                                                                    SQL_SELECT_RAWDATA_GETORDERS, param);

            while (dr.DataReader.Read())
            {
                Order order = new Order();

                if (dr.DataReader[0] != DBNull.Value)
                {
                    order.Date = dr.DataReader.GetDateTime(0);
                }

                if (dr.DataReader[1] != DBNull.Value)
                {
                    order.SymbolCode = dr.DataReader.GetString(1).Trim();
                }

                if (dr.DataReader[2] != DBNull.Value)
                {
                    order.MarketCode = dr.DataReader.GetString(2).Trim();
                }

                if (dr.DataReader[3] != DBNull.Value)
                {
                    order.OrderNumber = dr.DataReader.GetString(3).Trim();
                }

                if (dr.DataReader[4] != DBNull.Value)
                {
                    order.Price = dr.DataReader.GetDecimal(4);
                }

                if (dr.DataReader[5] != DBNull.Value)
                {
                    order.Volume = Convert.ToUInt32(dr.DataReader.GetInt64(5));
                }

                if (dr.DataReader[6] != DBNull.Value)
                {
                    order.Value = dr.DataReader.GetDecimal(6);
                }

                if (dr.DataReader[7] != DBNull.Value)
                {
                    order.LeValue = dr.DataReader.GetDecimal(7);
                }

                if (dr.DataReader[8] != DBNull.Value)
                {
                    order.Time = dr.DataReader.GetDateTime(8).TimeOfDay;
                }

                if (dr.DataReader[9] != DBNull.Value)
                {
                    string orderType = dr.DataReader.GetString(9);

                    if (orderType.Equals("buy"))
                    {
                        order.OrderType = QuoteType.Buy;
                    }
                    else
                    {
                        order.OrderType = QuoteType.Sell;
                    }
                }

                if (dr.DataReader[10] != DBNull.Value)
                {
                    order.Exchange = dr.DataReader.GetString(10).Trim();
                }

                if (dr.DataReader[11] != DBNull.Value)
                {
                    string orderAction = dr.DataReader.GetString(11);

                    if (orderAction.Equals("open"))
                    {
                        order.OrderAction = QuoteStatus.Open;
                    }
                    else
                    {
                        order.OrderAction = QuoteStatus.Matched;
                    }
                }

                if (dr.DataReader[12] != DBNull.Value)
                {
                    order.RecSerial = Convert.ToInt32(dr.DataReader.GetDecimal(12));
                }

                //if (dr.DataReader[13] != DBNull.Value)
                //{
                //    order.PreviousVolume = Convert.ToUInt32(dr.DataReader.GetInt64(13));
                //}

                orders.Add(order);
            }

            dr.DataReader.Close();

            dr.Dispose();
            dr = null;

            return orders;
        }

        public List<Trade> GetTrades(string lastTicketNumber)
        {
            List<Trade> trades = new List<Trade>();
            OracleSafeDataReader dr;

            OracleParameter param = new OracleParameter(":LAST_TICKET_NUMBER", OracleDbType.Varchar2, 25);
            param.Value = lastTicketNumber;

            dr = VFNStockTrader.DAL.Oracle.Helper.Oracle.ExecuteReaderSingleParm(this._internalConnection, null, CommandType.Text,
                                                                    SQL_SELECT_RAWDATA_GETTRADES, param);

            while (dr.DataReader.Read())
            {
                Trade trade = new Trade();

                if (dr.DataReader[0] != DBNull.Value)
                {
                    trade.Date = dr.DataReader.GetDateTime(0);
                }

                if (dr.DataReader[1] != DBNull.Value)
                {
                    trade.SymbolCode = dr.DataReader.GetString(1).Trim();
                }

                if (dr.DataReader[2] != DBNull.Value)
                {
                    trade.MarketCode = dr.DataReader.GetString(2).Trim();
                }

                if (dr.DataReader[3] != DBNull.Value)
                {
                    trade.TicketNumber = dr.DataReader.GetString(3).Trim();
                }

                if (dr.DataReader[4] != DBNull.Value)
                {
                    trade.Price = dr.DataReader.GetDecimal(4);
                }

                if (dr.DataReader[5] != DBNull.Value)
                {
                    trade.Volume = Convert.ToUInt32(dr.DataReader.GetInt64(5));
                }

                if (dr.DataReader[6] != DBNull.Value)
                {
                    trade.Value = dr.DataReader.GetDecimal(6);
                }

                if (dr.DataReader[7] != DBNull.Value)
                {
                    trade.LeValue = dr.DataReader.GetDecimal(7);
                }

                if (dr.DataReader[8] != DBNull.Value)
                {
                    trade.Time = dr.DataReader.GetDateTime(8).TimeOfDay;
                }

                if (dr.DataReader[9] != DBNull.Value)
                {
                    trade.BuyOrderNumber = dr.DataReader.GetString(9).Trim();
                }

                if (dr.DataReader[10] != DBNull.Value)
                {
                    trade.SellOrderNumber = dr.DataReader.GetString(10).Trim();
                }

                trades.Add(trade);
            }

            dr.DataReader.Close();

            dr.Dispose();
            dr = null;

            return trades;
        }

        public List<SymbolSummary> GetSymbolSummary(long totalTrades)
        {
            List<SymbolSummary> symbolSummary = new List<SymbolSummary>();
            OracleSafeDataReader dr;

            OracleParameter param = new OracleParameter(":TotalTrades", OracleDbType.Int32);
            param.Value = totalTrades;

            dr = VFNStockTrader.DAL.Oracle.Helper.Oracle.ExecuteReaderSingleParm(this._internalConnection, null, CommandType.Text,
                                                                    SQL_SELECT_SYMBOLSUMMARY_GETSYMBOLSUMMARY, param);

            while (dr.DataReader.Read())
            {
                SymbolSummary symbolSummaryItem = new SymbolSummary();

                if (dr.DataReader[0] != DBNull.Value)
                {
                    symbolSummaryItem.SymbolCode = dr.DataReader.GetString(0).Trim();
                }

                if (dr.DataReader[1] != DBNull.Value)
                {
                    symbolSummaryItem.ClosePrice = dr.DataReader.GetDouble(1);
                }

                if (dr.DataReader[2] != DBNull.Value)
                {
                    symbolSummaryItem.CumNumTransact = dr.DataReader.GetInt64(2);
                }

                if (dr.DataReader[3] != DBNull.Value)
                {
                    symbolSummaryItem.CumTradeValue = dr.DataReader.GetDouble(3);
                }

                if (dr.DataReader[4] != DBNull.Value)
                {
                    symbolSummaryItem.CumVolume = dr.DataReader.GetInt64(4);
                }

                if (dr.DataReader[5] != DBNull.Value)
                {
                    symbolSummaryItem.HighPrice = dr.DataReader.GetDecimal(5);
                }

                if (dr.DataReader[6] != DBNull.Value)
                {
                    symbolSummaryItem.LowPrice = dr.DataReader.GetDecimal(6);
                }

                if (dr.DataReader[7] != DBNull.Value)
                {
                    symbolSummaryItem.RefPrice = dr.DataReader.GetDecimal(7);
                }

                if (dr.DataReader[8] != DBNull.Value)
                {
                    symbolSummaryItem.SymbolType = dr.DataReader.GetInt16(8);
                }

                if (dr.DataReader[9] != DBNull.Value)
                {
                    symbolSummaryItem.RefTimeSpan = dr.DataReader.GetDateTime(9);
                }
                symbolSummary.Add(symbolSummaryItem);
            }

            dr.DataReader.Close();

            dr.Dispose();
            dr = null;

            return symbolSummary;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            try
            {
                if (this._internalConnection != null)
                {
                    this.Close();
                    this._internalConnection.Dispose();
                    this._internalConnection = null;
                }
            }
            catch (Exception ex)
            {
            }
        }

        #endregion
    }
}
